package org.springsource.dao.impl;

import java.util.List;

import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;
import org.springsource.dao.TeamDao;
import org.springsource.domain.Level;
import org.springsource.domain.Team;
import org.springsource.util.PlayerOrderExpression;

@Repository
public class TeamDaoImpl extends AbstractJpaDao<Team> implements TeamDao {
	
	public TeamDaoImpl(){
	      setClazz(Team.class );
	}
	@Override
	public List<Team> getAll() {
		return super.findAll();
	}

	@Override
	public Team getTeam(Long id) {
		return super.findOne(id);
	}
	
	@Override
	public Team getTeamByName(String name) {
		return entityManager
				.createQuery("SELECT t FROM Team t WHERE t.name = :name",
						Team.class).setParameter("name", name)
				.getSingleResult();
	}
	
	@Cacheable(value = { "teamsCache" })
	@Override
	public List<Team> getTeamsByCountry(String countryCode) {
		return entityManager
				.createQuery(
						"SELECT t FROM Team t WHERE t.country.code = :countryCode order by t.name",
						Team.class).setParameter("countryCode", countryCode)
				.getResultList();
	}
	@Override
	public Team saveTeam(Team team) {
		super.save(team);
		return team;
	}

	@Override
	public List<Team> getTeamsByLevelName(String levelName) {
		return entityManager
				.createQuery(
						"SELECT t FROM Team t WHERE t.level.name = :levelName order by t.name",
						Team.class).setParameter("levelName", levelName)
				.getResultList();
	}
	/**
	 * ORIGINAL SQL : select distinct t.name,p.firstName from Team t inner join Player p on t.id=p.team
		where t.name='Manchester City';
		
		select distinct t.name,p.firstName,c.name from Team t left join Player p 
		on t.id=p.team join Country c 
		on c.code=t.countryCode
		where t.name='Everton';
	 */
	@Override
	public Team getTeamWithPlayers(String name) {
		
		return entityManager
			.createQuery(
					"select distinct t from Team t left join fetch t.players where t.name = :name",
					Team.class).setParameter("name", name).getSingleResult();
	}
	
	@Override
	public Team getTeamWithPlayersOrderedBy(String name,
			PlayerOrderExpression exp) {
		//http://xircles.codehaus.org/projects/quaere
		//http://code.google.com/p/lambdaj/
		//http://stackoverflow.com/questions/1217228/what-is-the-java-equivalent-for-linq
		System.out.println("order by"+exp.getName());
		Team team = entityManager
				.createQuery(
						"select distinct t from Team t left join fetch  t.players p where t.name =" +
						" :name order by "+exp.getName(),
						Team.class).setParameter("name", name).getSingleResult();
		return team;
	}
	
	@Override
	public List<Team> getPremierLeagueTeams() {
		Level level =  entityManager
				.createQuery(
						"select distinct l from Level l left join fetch l.teams as ts where l.id = :id order by ts.name",
						Level.class).setParameter("id", new Long(1)).getSingleResult();
		return level.getTeams();
	}
	@Override
	public List<Team> getPrimeraDivisionTeams() {
		Level level =  entityManager
				.createQuery(
						"select distinct l from Level l left join fetch l.teams as ts where l.id = :id order by ts.name",
						Level.class).setParameter("id", new Long(7)).getSingleResult();
		return level.getTeams();
	}
	


}
